from app import db, app
from sqlalchemy.ext.associationproxy import association_proxy
import json
import datetime
from config import DATEFORMAT
from utils import debug

ROLE_USER = 0
ROLE_ADMIN = 1

from sqlalchemy import orm

# This is course tree structure. Make sure that once science domain is delted, maybe we can  set -key
# as it's primary key.
class Department(db.Model):
	__tablename__ = u'department'
	departmentid = db.Column(db.Integer, primary_key=True)
	departmenttitle = db.Column(db.String, index=True)

	sciencedomains = db.relationship('ScienceDomain', backref='parentdepartment')
	# This will not be used. This was picked up from the legacy system.
	# Normaly we will have one to many relationship. Thus, we do not need an association table.
	#sciencedomain = db.relationship('sciencedomaincourses', secondary=departmentassociationtable)
	def __repr__(self):
		return ('<department id=%s, title=%s>' % (self.departmentid, self.departmenttitle)).encode('utf-8')

	def exportasJSON(self, coding='utf-8'):
		departmentJson = {key: value for key, value in vars(self).items() if 'department' in key}
		departmentJson['sciencedomains'] = {}
		for sd in self.sciencedomains:
			departmentJson['sciencedomains'][sd.sciencedomainid] = sd.exportasJSON(coding)

		return departmentJson

class ScienceDomain(db.Model):
	__tablename__ = u'sciencedomain'
	sciencedomainid = db.Column(db.Integer, primary_key=True)
	sciencedomaintitle = db.Column(db.String, index=True)

	departmentid = db.Column(db.Integer, db.ForeignKey('department.departmentid'))
	courses = db.relationship('Course', backref='parentsciencedomain')
	#The same as for department. This was picked up from the legacy system.
	#course = db.relationship('sciencedomaincourses', secondary=departmentassociationtable)
	def __repr__(self):
		return ('<sciencedomain id=%s, title=%s>' % (self.sciencedomainid, self.sciencedomaintitle)).encode('utf-8')

	def exportasJSON(self, coding='utf-8'):
		sdomainsJSON = {key: value for key, value in vars(self).items() if 'sciencedomain' in key}
		sdomainsJSON['courses'] = {}
		for course in self.courses:
			sdomainsJSON['courses'][course.courseid] = course.exportasJSON(coding)

		return sdomainsJSON


# Course details. Add whoosh for searching
class Course(db.Model):
	__tablename__ = u'course'
	def __init__(self):
		self.employeecoursestartdate = ''
		self.employeecourseenddate = ''

	courseid = db.Column(db.Integer, primary_key=True)
	coursetitle = db.Column(db.String, index=True)

	sciencedomainid = db.Column(db.Integer, db.ForeignKey('sciencedomain.sciencedomainid'))

	def __repr__(self):
		return ('<course id=%s, title=%s>' % (self.courseid, self.coursetitle)).encode('utf-8')

	def exportasJSON(self, coding='utf-8'):
		return {key: value for key, value in vars(self).items() if 'course' in key}

# Job tables
class Job(db.Model):
	# Teaching staff (Full professors, Associate professors, Assistant professors, Assistants, Teaching associates)
	# Non teaching staff
	# Contractors
	# Associates
	# Retired
	# Inactive
	__tablename__ = u'job'
	jobid = db.Column(db.Integer, primary_key=True)
	jobtitle = db.Column(db.Integer, unique=True)

	jobtypes = db.relationship('JobType', backref='parentjob')

	def exportasJSON(self, coding='utf-8'):
		jobJSON = {key: value for key, value in vars(self).items() if 'job' in key}
		jobJSON['jobtypes'] = {}
		for jobtype in self.jobtypes:
			jobJSON['jobtypes'][jobtype.jobtypeid] = jobtype.exportasJSON(coding)

		return jobJSON

	def __repr__(self):
		return ('<job id=%s, title=%s>' % (self.jobid, self.jobtitle)).encode('utf-8')

class JobType(db.Model):
	# Subpositions from the Job table.
	__tablename__ = u'jobtype'
	def __init__(self):
		self.employeejobtypestartdate = ''
		self.employeejobtypeexpirationdate = ''
		self.employeejobtypeactive = ''

	jobtypeid = db.Column(db.Integer, primary_key=True)
	jobid = db.Column(db.Integer, db.ForeignKey('job.jobid')) # Refer to base table
	jobtypetitle = db.Column(db.String(128))
	jobtypeduration = db.Column(db.Integer, default=0) # Duration to reelection. 4yrs for professors
	jobtypebasepoints = db.Column(db.Integer, default=0)
	jobtypecoefficient = db.Column(db.Integer, default=0)

	def exportasJSON(self, coding='utf-8'):
		#app.logger.info('exporting jobtype')
		return {key: value for key, value in vars(self).items() if 'jobtype' in key}

	def __repr__(self):
		return ('<jobtype id=%s, title=%s>' % (self.jobtypeid, self.jobtypetitle)).encode('utf-8')

class Employee(db.Model):
	@orm.reconstructor
	def init(self):
		if self.employeejobdate:
			pass

	# Personal data
	employeeid = db.Column(db.Integer, primary_key=True)
	employeefirstname = db.Column(db.String(64), index=True)
	employeemidname = db.Column(db.String(64))
	employeesurname = db.Column(db.String(64), index=True)
	employeegender = db.Column(db.CHAR)
	employeejmbg = db.Column(db.String(), unique=True) # hashed value goes here for privacy reasons.
	employeepersonalcardnumber = db.Column(db.String, default='')
	employeesecuritysocialnumber = db.Column(db.String(20)) # Broj zdravstevnog osiguranja. LBO.
	employeephoto = db.Column(db.String, default='nophoto.jpg') # Location of an image on a disk.
	# Admin data
	employeerole = db.Column(db.SmallInteger,default=ROLE_USER)
	employeeemail = db.Column(db.String(128), default='')

	# Birth data
	employeedateofbirth = db.Column(db.DateTime)
	employeecountryofbirth = db.Column(db.String(128), default = '')
	employeestateofbirth = db.Column(db.String(128), default = '')
	employeedistrictofbirth = db.Column(db.String(128), default='')
	employeecityofbirth = db.Column(db.String(128), default = '')

	# Addresses
	employeestreet = db.Column(db.String(256), default = u'')
	employeecity = db.Column(db.String(256), default = u'')
	employeedistrict = db.Column(db.String(256), default = u'')
	employeestate = db.Column(db.String(128), default = u'')

	# Education
	#employeeeducations = db.relationship('EducationLevel', secondary='employeeeducation')
	employeeeducations = association_proxy('employeeeducationmembership', 'educationlevel', creator=lambda educationlevelwithdetails: EmployeeEducation(educationlevelwithdetails))
	# employeeeducationlevel = db.Column(db.SmallInt, db.ForeignKey('education.educationid')) # Reference table education

	# Jobs
	employeejobs = association_proxy('employeejobmembership', 'jobtype', creator=lambda jobtypewithdetails: EmployeeJobType(jobtypewithdetails))
	employeejobdate = db.Column(db.DateTime) # The date when employee got hired.
	employeejobstatus = db.Column(db.SmallInteger) # Active, retired...

	# Courses.
	# TODO: Course doesn't need to have employee in its table.
	# TODO: Maybe to use asociative table instead?
	employeecourses = association_proxy('employeecoursemembership', 'course', creator=lambda coursewithdetails: EmployeeCourse(coursewithdetails))

	#Misc. This is the string that contains what was in legacy database tables: employeejobdetails, pregnancy, stagnation
	#
	employeecustomproperties = db.Column(db.String)

	# Working data. How long employee has been working.
	# This doesn't belong to database
	employeeworktotal = None # measured in ms
	employeeworkingdays = None
	employeeworkingyears = None
	employeeworkingmonths = None
	employeejobexpirationdate = None # expiration date
	employeeeducationlevel = None # This doesen't need to be stored in database. We take the highest one form employeeeducation table

	def exportasJSON(self, coding='utf-8'):
		ed = {key: value for key, value in vars(self).items() if 'employee' in key} # We only use employees fields, not sql specific stuff
		for key, value in ed.items():
			if type(value) in [unicode]:
				ed[key] = value.encode(coding)
			elif type(value) in [datetime.datetime]:
				ed[key] = value.strftime(DATEFORMAT)

		ed['employeejobs'] = []
		ed['employeeeducation'] = []
		ed['employeecourses'] = []

		for employeejob in self.employeejobmembership:
			if employeejob:
				employeeJobJSON = employeejob.exportasJSON(coding)
				employeeJobJSON['jobtypetitle'] = JobType.query.get(employeejob.jobtypeid).jobtypetitle
				app.logger.info('employeejobjson = ' + str(employeeJobJSON))
				ed['employeejobs'].append(employeeJobJSON)
		for education in self.employeeeducationmembership:
			if education:
				ed['employeeeducation'].append(education.exportasJSON(coding))
		for course in self.employeecoursemembership:
			if course:
				ed['employeecourses'].append(course.exportasJSON(coding))

		return json.dumps(ed)
	def __repr__(self):
		return ('<employee id=%s, fname=%s surname=%s>' % (self.employeeid, self.employeefirstname, self.employeesurname)).encode('utf-8')


# This table contains data about courses an employee lectures
class EmployeeCourse(db.Model):
	__tablename__ = u'employeecourse'
	def __init__(self, course):
		self.course = course
		self.employeecoursestartdate = course.employeecoursestartdate
		self.employeecourseenddate = course.employeecourseenddate

	employeecourseid = db.Column(db.Integer, primary_key=True)
	employeeid = db.Column(db.Integer, db.ForeignKey('employee.employeeid'))
	courseid = db.Column(db.Integer, db.ForeignKey('course.courseid'))
	employeecoursestartdate = db.Column(db.DateTime)
	employeecourseenddate = db.Column(db.DateTime)

	employee = db.relationship(Employee, backref='employeecoursemembership')
	course = db.relationship(Course, backref='employeecoursemembership')

	@debug
	def exportasJSON(self, coding='utf-8'):
		print 'employeeCourse.exportasJSON()'
		return {}

	def __repr__(self):
		return ('<employeecourse employeeid=%s, courseid=%s>' % (self.employee, self.course)).encode('utf-8')


# Table with education data. This applies in Serbia, 7 grades of education
class EducationLevel(db.Model):
	__tablename__ = u'educationlevel'

	def __init__(self):
		# Those fields belong to employeeeducation table.
		# But I declared themhere intentionally as EmployeeEducation is created on EducationLevel
		# when employee.educations.append(educationlevel), thus this is the way to ship all together.
		employeeeducationfacultytitle = u''
		employeeeducationgraduatedate = u''
		employeeeducationdepartment = u''
		employeeeducationscienceinteresttitle = u''

	educationlevelid = db.Column(db.Integer, primary_key=True)
	educationleveltitle = db.Column(db.String, unique=True)
	educationleveltag = db.Column(db.String, default=u'')

	employees = association_proxy('employeeeducationmembership', 'employee')

	def exportasJSON(self, coding='utf-8'):
		educationlevel = {key: value for key, value in vars(self).items() if 'educationlevel' in key}
		for key, value in educationlevel.items():
			if type(value) in [unicode]:
				educationlevel[key] = value.encode(coding)
			elif type(value) in [datetime.datetime]:
				educationlevel[key] = value.strftime(DATEFORMAT)

		return educationlevel

	def __repr__(self):
		return ('<educationlevel id=%s, title=%s>' % (self.educationlevelid, self.educationleveltitle)).encode('utf-8')

class EmployeeJobType(db.Model):
	__tablename__ = 'employeejobtype'
	def __init__(self, jobtype):
		self.jobtype = jobtype
		# Those fields originally do not belong to jobtype. However, we will bundle them together
		# because we create employeejobtype from jobtype when we append to an employee's jobtype list
		self.employeejobtypestartdate = jobtype.employeejobtypestartdate
		self.employeejobtypeexpirationdate = jobtype.employeejobtypeexpirationdate
		self.employeejobtypeactive = jobtype.employeejobtypeactive

	employeejobtypeid = db.Column(db.Integer, primary_key=True)
	employeeid = db.Column(db.Integer, db.ForeignKey('employee.employeeid'))
	jobtypeid = db.Column(db.Integer, db.ForeignKey('jobtype.jobtypeid'))
	employeejobtypestartdate = db.Column(db.DateTime)
	employeejobtypeexpirationdate = db.Column(db.DateTime)
	employeejobtypeactive = db.Column(db.Integer)

	employee = db.relationship(Employee, backref='employeejobmembership')
	jobtype = db.relationship(JobType, backref='employeejobmembership')

	@debug
	def exportasJSON(self, coding='utf-8'):
		#app.logger.info('exporting EmployeeJobType')
		# We are looking for table data. SQLAlchemy data we do not export, thus employeejobtype in key clause.
		employeejob = {key: value for key, value in vars(self).items() if 'employeejobtype' in key}
		employeejob['jobtypeid'] = self.jobtypeid # Put jobtypeid explicetly
		#employeejob['employeejobtypestartdate'] = self.employeejobtypestartdate
		#employeejob['employeejobtypeexpirationdate'] = self.employeejobtypeexpirationdate
		for key, value in employeejob.items():
			if type(value) in [unicode]:
				employeejob[key] = value.encode(coding)
			elif type(value) in [datetime.datetime]:
				employeejob[key] = value.strftime(DATEFORMAT)

		return employeejob

	def __repr__(self):
		return ('<employeejobtype, employee=%s, jobtype=%s>' % (self.employee.employeeid, self.jobtype)).encode('utf-8')

# This table contains details of employee education. Many to Many asociation table
class EmployeeEducation(db.Model):
	def __init__(self, educationlevel):
		#self.educationlevel = educationlevel
		self.employeeeducationfacultytitle = educationlevel.employeeeducationfacultytitle
		self.employeeeducationgraduatedate = educationlevel.employeeeducationgraduatedate
		self.employeeeducationdepartment = educationlevel.employeeeducationdepartment
		self.employeeeducationscienceinteresttitle = educationlevel.employeeeducationscienceinteresttitle

	__tablename__ = 'employeeeducation'
	employeeeducationid = db.Column(db.Integer, primary_key=True)
	employeeid = db.Column(db.Integer, db.ForeignKey('employee.employeeid'))
	educationlevelid = db.Column(db.Integer, db.ForeignKey('educationlevel.educationlevelid'))
	employeeeducationfacultytitle = db.Column(db.String)
	employeeeducationgraduatedate = db.Column(db.DateTime)
	employeeeducationdepartment = db.Column(db.String)
	employeeeducationscienceinteresttitle = db.Column(db.String)

	employee = db.relationship(Employee, backref='employeeeducationmembership')
	educationlevel = db.relationship(EducationLevel, backref='employeeeducationmembership')

	@debug
	def exportasJSON(self, coding='utf-8'):
		app.logger.info('exporting EmployeeEducation')
		employeeEducation = {key: value for key, value in vars(self).items() if 'employeeeducation' in key}
		#employeeEducation['employeejobtypestartdate'] = self.employeejobtypestartdate
		#employeeEducation['employeejobtypeexpirationdate'] = self.employeejobtypeexpirationdate
		for key, value in employeeEducation.items():
			if type(value) in [unicode]:
				employeeEducation[key] = value.encode(coding)
			elif type(value) in [datetime.datetime]:
				employeeEducation[key] = value.strftime(DATEFORMAT)

		return employeeEducation

	def __repr__(self):
		return ('<employeeeducation, employee=%s, educationlevel=%s>' % (self.employee, self.educationlevel)).encode('utf-8')
